import sqlite3 as sq3
import pandas as pd
import numpy as np
import plotly.express as px
con = sq3.connect("opioid.db")
annua = pd.read_sql_query("SELECT * from annual", con)
lan = pd.read_sql_query("SELECT * from land", con)
populatio = pd.read_sql_query("SELECT * from population", con)
# you have to close the connection
con.close
annual=pd.DataFrame(annua)
land=pd.DataFrame(lan)
population=pd.DataFrame(populatio)
population
| BUYER_COUNTY | BUYER_STATE | countyfips | STATE | COUNTY | county_name | NAME | variable | year | population | ||
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | AUTAUGA | AL | 01001 | 1 | 1 | Autauga | Autauga County, Alabama | B01003_001 | 2006 | 51328 |
| 1 | 2 | BALDWIN | AL | 01003 | 1 | 3 | Baldwin | Baldwin County, Alabama | B01003_001 | 2006 | 168121 |
| 2 | 3 | BARBOUR | AL | 01005 | 1 | 5 | Barbour | Barbour County, Alabama | B01003_001 | 2006 | 27861 |
| 3 | 4 | BIBB | AL | 01007 | 1 | 7 | Bibb | Bibb County, Alabama | B01003_001 | 2006 | 22099 |
| 4 | 5 | BLOUNT | AL | 01009 | 1 | 9 | Blount | Blount County, Alabama | B01003_001 | 2006 | 55485 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 28260 | 28261 | WASHAKIE | WY | 56043 | 56 | 43 | Washakie | Washakie County, Wyoming | B01003_001 | 2014 | 8444 |
| 28261 | 28262 | WESTON | WY | 56045 | 56 | 45 | Weston | Weston County, Wyoming | B01003_001 | 2014 | 7135 |
| 28262 | 28263 | SKAGWAY | AK | 02230 | 2 | 230 | Skagway | Skagway Municipality, Alaska | B01003_001 | 2014 | 996 |
| 28263 | 28264 | HOONAH ANGOON | AK | 02105 | 2 | 105 | Hoonah Angoon | Hoonah-Angoon Census Area, Alaska | B01003_001 | 2014 | 2126 |
| 28264 | 28265 | PETERSBURG | AK | 02195 | 2 | 195 | Petersburg | Petersburg Borough, Alaska | B01003_001 | 2014 | 3212 |
28265 rows × 11 columns
NA=annual.loc[annual.countyfips == 'NA']
NA
| BUYER_COUNTY | BUYER_STATE | year | count | DOSAGE_UNIT | countyfips | ||
|---|---|---|---|---|---|---|---|
| 187 | 188 | ADJUNTAS | PR | 2006 | 147 | 102800 | NA |
| 188 | 189 | ADJUNTAS | PR | 2007 | 153 | 104800 | NA |
| 189 | 190 | ADJUNTAS | PR | 2008 | 153 | 45400 | NA |
| 190 | 191 | ADJUNTAS | PR | 2009 | 184 | 54200 | NA |
| 191 | 192 | ADJUNTAS | PR | 2010 | 190 | 56200 | NA |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 27753 | 27754 | NA | NV | 2007 | 447 | 200600 | NA |
| 27754 | 27755 | NA | NV | 2008 | 5 | 2200 | NA |
| 27755 | 27756 | NA | OH | 2006 | 23 | 5100 | NA |
| 27756 | 27757 | NA | PR | 2006 | 10 | 17800 | NA |
| 27757 | 27758 | NA | PR | 2007 | 2 | 1300 | NA |
760 rows × 7 columns
annual.countyfips = np.where((annual.BUYER_COUNTY == 'MONTGOMERY') & (annual.BUYER_STATE == 'AR'),'05097',annual.countyfips)
annual = annual.drop(annual[annual.countyfips == 'NA'].index)
annual
| BUYER_COUNTY | BUYER_STATE | year | count | DOSAGE_UNIT | countyfips | ||
|---|---|---|---|---|---|---|---|
| 0 | 1 | ABBEVILLE | SC | 2006 | 877 | 363620 | 45001 |
| 1 | 2 | ABBEVILLE | SC | 2007 | 908 | 402940 | 45001 |
| 2 | 3 | ABBEVILLE | SC | 2008 | 871 | 424590 | 45001 |
| 3 | 4 | ABBEVILLE | SC | 2009 | 930 | 467230 | 45001 |
| 4 | 5 | ABBEVILLE | SC | 2010 | 1197 | 539280 | 45001 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 27736 | 27737 | ZAVALA | TX | 2010 | 248 | 200100 | 48507 |
| 27737 | 27738 | ZAVALA | TX | 2011 | 406 | 244800 | 48507 |
| 27738 | 27739 | ZAVALA | TX | 2012 | 473 | 263700 | 48507 |
| 27739 | 27740 | ZAVALA | TX | 2013 | 399 | 186700 | 48507 |
| 27740 | 27741 | ZAVALA | TX | 2014 | 162 | 148930 | 48507 |
27007 rows × 7 columns
land1= land[['Areaname','STCOU','LND110210D']].copy()
land1.rename(columns = {'STCOU':'countyfips'}, inplace = True)
land1
| Areaname | countyfips | LND110210D | |
|---|---|---|---|
| 0 | UNITED STATES | 00000 | 3531905.43 |
| 1 | ALABAMA | 01000 | 50645.33 |
| 2 | Autauga, AL | 01001 | 594.44 |
| 3 | Baldwin, AL | 01003 | 1589.78 |
| 4 | Barbour, AL | 01005 | 884.88 |
| ... | ... | ... | ... |
| 3193 | Sweetwater, WY | 56037 | 10426.65 |
| 3194 | Teton, WY | 56039 | 3995.38 |
| 3195 | Uinta, WY | 56041 | 2081.26 |
| 3196 | Washakie, WY | 56043 | 2238.55 |
| 3197 | Weston, WY | 56045 | 2398.09 |
3198 rows × 3 columns
population.merge(land1, on='countyfips', how='left')
| BUYER_COUNTY | BUYER_STATE | countyfips | STATE | COUNTY | county_name | NAME | variable | year | population | Areaname | LND110210D | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | AUTAUGA | AL | 01001 | 1 | 1 | Autauga | Autauga County, Alabama | B01003_001 | 2006 | 51328 | Autauga, AL | 594.44 |
| 1 | 2 | BALDWIN | AL | 01003 | 1 | 3 | Baldwin | Baldwin County, Alabama | B01003_001 | 2006 | 168121 | Baldwin, AL | 1589.78 |
| 2 | 3 | BARBOUR | AL | 01005 | 1 | 5 | Barbour | Barbour County, Alabama | B01003_001 | 2006 | 27861 | Barbour, AL | 884.88 |
| 3 | 4 | BIBB | AL | 01007 | 1 | 7 | Bibb | Bibb County, Alabama | B01003_001 | 2006 | 22099 | Bibb, AL | 622.58 |
| 4 | 5 | BLOUNT | AL | 01009 | 1 | 9 | Blount | Blount County, Alabama | B01003_001 | 2006 | 55485 | Blount, AL | 644.78 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 28260 | 28261 | WASHAKIE | WY | 56043 | 56 | 43 | Washakie | Washakie County, Wyoming | B01003_001 | 2014 | 8444 | Washakie, WY | 2238.55 |
| 28261 | 28262 | WESTON | WY | 56045 | 56 | 45 | Weston | Weston County, Wyoming | B01003_001 | 2014 | 7135 | Weston, WY | 2398.09 |
| 28262 | 28263 | SKAGWAY | AK | 02230 | 2 | 230 | Skagway | Skagway Municipality, Alaska | B01003_001 | 2014 | 996 | Skagway, AK | 452.33 |
| 28263 | 28264 | HOONAH ANGOON | AK | 02105 | 2 | 105 | Hoonah Angoon | Hoonah-Angoon Census Area, Alaska | B01003_001 | 2014 | 2126 | Hoonah-Angoon, AK | 7524.92 |
| 28264 | 28265 | PETERSBURG | AK | 02195 | 2 | 195 | Petersburg | Petersburg Borough, Alaska | B01003_001 | 2014 | 3212 | Petersburg, AK | 3281.98 |
28265 rows × 13 columns
annual['DOSAGE_UNIT'] = pd.to_numeric(annual['DOSAGE_UNIT'], downcast="float")
annual=annual.groupby('year')['DOSAGE_UNIT'].mean().reset_index()
fig=px.scatter(annual, x='year', y='DOSAGE_UNIT')
fig.write_image("fig2.png")
fig.show()